package com.minus.remove;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Minus_Remove {

	// 以下是MySQL的驱动连接
	public static Connection MySQL_conn = null;
	static PreparedStatement MySQL_statement = null;

	public static String MySQL_URL = "jdbc:mysql://localhost:3306/20120912";
	public static String MySQL_DRIVER = "com.mysql.jdbc.Driver";
	public static String username = "root";
	public static String password = "chaick1989";

	public static int sum = 0;
	public static ResultSet rs_filter = null;
	public static ResultSet rs_filter_2 = null;

	public static ResultSet rs = null;

	public static void main(String[] args) {
		// TODO Auto-generated method stub

		try {

			Connect();// 打开mysql的数据库连接
			String str_query = "select eid,phone from tb_enterprise where phone like '%-%'";
			String str_tb_enterprise_filter = null;

			rs = selectSQL(str_query);

			String tmp_phone = null;
			String tmp_phone_fixed = null;

			String tmp_filter_display = null;

			int tmp_eid;
			String update_sql = null;
			int i = 0;

			update_sql = "update tb_enterprise set phone = ? where eid =?";

			MySQL_statement = (PreparedStatement) MySQL_conn.prepareStatement(update_sql);
			MySQL_conn.setAutoCommit(false);

			while (rs.next()) {
				tmp_eid = rs.getInt("eid");
				tmp_phone = rs.getString("phone");

				i++;

				tmp_phone_fixed = Remove_Minus(tmp_phone);
				
				MySQL_statement.setString(1, tmp_phone_fixed);
				MySQL_statement.setInt(2, tmp_eid);

				MySQL_statement.addBatch();

				if (i % 500 == 0) { // 每预编译30个sql语句，提交一次。
					MySQL_statement.executeBatch();
					MySQL_conn.commit();
					if (null == MySQL_conn) { // 如果连接关闭了 就在创建一个 为什么要这样
														// 原因是conn.commit()后可能conn被关闭
						Connect();
						MySQL_conn.setAutoCommit(false);
					}
					MySQL_statement.clearBatch();
				}
				if (i % 500 == 0) {
					System.out.println("执行了条数" + i);
				}
			}
			
			if (MySQL_statement != null) {
				MySQL_statement.executeBatch();
				MySQL_conn.commit();
			}

			System.out.println("总共执行了数" + i);

			MySQL_conn.setAutoCommit(true);
			MySQL_statement.close();
			Disconnect();

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	static void Connect() {

		try {
			Class.forName(MySQL_DRIVER);
			MySQL_conn = DriverManager.getConnection(MySQL_URL, username,
					password);
		}

		catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			System.out.println("JDBC 数据库驱动加载失败！！");
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("无法连接到数据库@@@");
			e.printStackTrace();
		}

	}

	static void Disconnect() {
		try {
			if (MySQL_conn != null)
				MySQL_conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("数据库关闭失败！！！！");
			e.printStackTrace();
		}

	}

	static ResultSet selectSQL(String sql) {
		ResultSet rs = null;
		PreparedStatement MySQL_statement_select = null;

		try {
			MySQL_statement_select = (PreparedStatement) MySQL_conn
					.prepareStatement(sql);
			// MySQL_statement.execute("set names utf8");
			rs = MySQL_statement_select.executeQuery(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}

	static boolean updateSQL(String sql) {
		try {
			MySQL_statement = (PreparedStatement) MySQL_conn
					.prepareStatement(sql);
			MySQL_statement.executeUpdate();
			// MySQL_statement.executeBatch();
			if (MySQL_statement != null) {
				MySQL_statement.close();
			}
			return true;
		} catch (SQLException e) {
			System.out.println("更新数据库时出错：");
			e.printStackTrace();
		} catch (Exception e) {
			System.out.println("更新时出错：");
			e.printStackTrace();
		}
		return false;
	}
	
	
	static String Convert_Dan(String str){
		String str_converted = null;
		str_converted = str.replaceAll("'", "\\\\'");
		return str_converted;
		
	}
	
	static String Replace_Dan(String str){
		String str_converted = null;
		str_converted = str.replace("'", "-");
		return str_converted;
	}
	
	static String Convert_Shuang(String str){
		String str_converted = null;
		str.replace('"', '\"');
		str_converted = str;
		return str_converted;
	}
	
	static String Remove_Minus(String str){
		String str_fixed = null;
	    str_fixed = str.replace("-","");
		return str_fixed;
		
	}
	
	
	static String Remove_KuoHao(String str){
		String str_fixed = null;
		return str_fixed;
		
	}
	
	
	
	
	
	
	
	
	
	
	
	
	

}
